

-------------------------1
use master 
go
create database bbs
on
	(
	name='bbs',
	filename='F:\数据库文件\bbs.mdf',
	size=5,
	maxsize=9,
	filegrowth=3
	)
log on
	(
	name='bbs_log',
	filename='F:\数据库文件\bbs_log.ldf',
	size=5,
	maxsize=9,
	filegrowth=3
	)

	go 
	use bbs
	create table bbsUsers
	(
	userID int not null, 
	userName varchar(10)  not null,
	userSex varchar(2)  not null,
	userAge int not null,
	userPoint int  not null 
	)
	go
	alter table bbsUsers 
	add constraint PK_userID primary key (userID)

	alter table bbsUsers
	add constraint UQ_userName unique (userName)

	alter table bbsUsers
	add constraint CK_userSex check(userSex='男' or userSex='女')

	alter table bbsUsers
	add constraint CK_userAge check(userAge>=15 and userAge<=60)

	alter table bbsUsers
	add constraint CK_userPoint check(userPoint>=0)
	go
	create table bbsSection
	(
	SectionID int primary key identity(1,1) not null,
	SectionName varchar(10) not null,
	SectionUid int references bbsUsers(UserID)
	)
	 go
	 use bbs
	create table bbsTopic
	(
	tID int primary key identity(1,1),
	userID int references bbsUsers(userID),
	SectionID int references bbsSection(SectionID) not null,
	tTitle varchar(100) not null,
	tMsg text not null,
	tTime datetime ,
	tCount int null,
	)
	go
	create table bbsReply
	(
	rID int primary key identity(1,1),
	rUID int references bbsUsers(UserID) ,
	rTID int references bbsTopic(tID) ,
	rMsg text not null,
	rTime datetime
	)

-------------------------2
	use bbs
	go
	insert into bbsUsers values
	('1', '小雨点','女','20','0'),
	('2','逍遥','男','18','4'),
	('3','七年级生','男','19','2')

	select userName 用户名 ,userPoint 积分 from bbsUsers

	insert into bbsSection(SectionName,SectionUid)
	select '技术交流',1 union
	select '读书世界',2 union
	select '生活百科',1 union
	select '八卦区',2

	select * from bbsTopic
	select *from bbsSection
	insert into bbsTopic(tID,userID,tTitle,tMsg,tTime,tCount)
	select 1,3,'今年夏天最流行什么 ','有谁知道今年夏天最流行什么呀？','2008-9-10',0
	select 2,4,'范跑跑','谁是范跑跑','2008-7-8',1
	select 3,1,'.NET','与JAVA的区别是什么呀？','2008-9-1',2

	select * from bbsReply
	insert into bbsReply(rUID,rTID,rMsg,rTime)
	select 1,1,'阿巴','2008-7-8' union
	select 2,2,'阿巴阿巴','2008-7-8' union
	select 3,3,'阿巴阿巴阿巴','2008-9-1'

	select * from bbsSection
	select * from bbsTopic
	delete from bbsUsers where UserID=2
	delete from bbsSection where SectionUid=2
	delete from bbsTopic where tID=1
	delete from bbsTopic where userID=2
	delete from bbsReply where rTID=5
	select * from bbsUsers

	update bbsUsers set userPoint=14 where userName='小雨点'
	delete from bbsSection where  SectionName='生活百科'
	truncate table bbsReply